﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Text;
using System.IO;
using System.Windows.Forms;

namespace CSLAGenTool
{
    public class DataAccess
    {
        public OleDbConnection cnn;
        public string strcon;
        public OleDbDataReader dataReader = null;

        // Constructor
        public DataAccess(string path)
        {
            string[] sTmp = File.ReadAllLines(path + "config.txt");
            strcon = sTmp[0];
            string s = OpenConnection();
        }

        // Mở CSDL
        public string OpenConnection()
        {
            try
            {
                cnn = new OleDbConnection(strcon);
                cnn.Open();
                return "";
            }
            catch (Exception ex) { return "Lỗi: " + ex.Message + "\r\n" + ex.TargetSite; }
        }

        // Đóng CSDL
        public void CloseConnection()
        {
            if (cnn.State != ConnectionState.Closed)
            {
                cnn.Close();
                cnn.Dispose();
            }
        }

        // Thực thi query
        public OleDbDataReader Exec(string scmd)
        {
            if (dataReader != null && !dataReader.IsClosed) dataReader.Close();
            dataReader = null;
            try
            {
                OleDbCommand sqlComm = new OleDbCommand(scmd, cnn);
                dataReader = sqlComm.ExecuteReader();
            }
            catch (Exception ex) { dataReader = null; }
            return dataReader;
        }

        //Thực thi lệnh
        public string ExecNonQuery(string scmd)
        {
            try
            {
                OleDbCommand sqlComm = new OleDbCommand(scmd, cnn);
                int i = sqlComm.ExecuteNonQuery();
                return "";
            }
            catch (Exception ex) { return "Lỗi: " + ex.Message + "\r\n" + ex.TargetSite; }
        }

        //Lấy toàn bộ thông tin của table
        public TableInfo GetTableInfo(string tableName)
        {
            string defaultValue = null;

            TableInfo tableInfo = new TableInfo();
            tableInfo.tableName = tableName;

            string primaryKey = GetPrimaryKey(tableName);

            DataTable table = this.cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, tableName, null });
            string[] s = new string[table.Rows.Count];
            for (int i = 0; i < s.Length; i++)
            {
                ColumnInfo columnInfo = new ColumnInfo();
                columnInfo.columnName = table.Rows[i].ItemArray[3].ToString();
                columnInfo.isNullable = (table.Rows[i].ItemArray[10].ToString() == "True" ? true : false);
                columnInfo.description = table.Rows[i].ItemArray[27].ToString();
                columnInfo.stringLength = -1;
                try
                {
                    columnInfo.stringLength = int.Parse(table.Rows[i].ItemArray[13].ToString());
                }
                catch { }

                columnInfo.variantType = convert2VariantType(int.Parse(table.Rows[i].ItemArray[11].ToString()));
                columnInfo.sqlDataType = convert2SqlDataType(int.Parse(table.Rows[i].ItemArray[11].ToString()));

                columnInfo.isPrimaryKey = (table.Rows[i].ItemArray[3].ToString() == primaryKey);
                columnInfo.isAutoID = columnInfo.isPrimaryKey;
                tableInfo.columnInfos.Add(columnInfo);
                //
                if (columnInfo.isPrimaryKey)
                {
                    tableInfo.primaryKey = columnInfo.columnName;
                    tableInfo.isAutoID = columnInfo.isAutoID;
                    tableInfo.variantType = columnInfo.variantType;
                    tableInfo.sqlDataType = columnInfo.sqlDataType;
                }
            }

            return tableInfo;
        }

        //Lấy các table trong batabase
        public string[] GetAllTableName()
        {
            DataTable table = this.cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            string[] s = new string[table.Rows.Count];
            for (int i = 0; i < table.Rows.Count; i++)
                s[i] = table.Rows[i]["TABLE_NAME"].ToString();
            return s;
        }

        public string[] GetAllTableNameOrderByName()
        {
            DataTable table = this.cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            string[] s = new string[table.Rows.Count];
            for (int i = 0; i < table.Rows.Count; i++)
                s[i] = table.Rows[i]["TABLE_NAME"].ToString();
            return s;
        }

        //Lấy tên các cột trong table
        public string[] GetAllColumnsName(string tableName)
        {
            DataTable table = this.cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, tableName, null });
            string[] s = new string[table.Columns.Count];
            for (int i = 0; i < s.Length; i++)
                s[i] = table.Rows[i].ItemArray[3].ToString();
            return s;
        }

        //Lấy PK của table
        public string GetPrimaryKey(string tableName)
        {
            string pk = null;
            DataTable table = this.cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new object[] { null, null, tableName });
            try
            {
                pk = table.Rows[0].ItemArray[3].ToString();
            }
            catch
            {
                pk = null;
            }
            return pk;
        }

        private string convert2VariantType(int dataType)
        {
            string result = "";
            switch (dataType)
            {
                case 2:
                    result = "int";
                    break;
                case 3:
                    result = "int";
                    break;
                case 4:
                    result = "Single";
                    break;
                case 5:
                case 6:
                    result = "double";
                    break;
                case 7:
                    result = "DateTime";
                    break;
                case 11:
                    result = "bool";
                    break;
                case 17:
                    result = "byte";
                    break;
                case 130:
                    result = "string";
                    break;
                case 131:
                    result = "decimal";
                    break;
                case 128:
                    result = "byte[]";
                    break;
            }
            return result;
        }

        private string convert2SqlDataType(int dataType)
        {
            string result = "";
            switch (dataType)
            {
                case 2:
                    result = "Integer";
                    break;
                case 3:
                    result = "Integer";
                    break;
                case 4:
                    result = "Single";
                    break;
                case 5:
                case 6:
                    result = "Double";
                    break;
                case 7:
                    result = "Date";
                    break;
                case 11:
                    result = "Boolean";
                    break;
                case 17:
                    result = "UnsignedTinyInt";
                    break;
                case 130:
                    result = "LongVarWChar";
                    break;
                case 131:
                    result = "Decimal";
                    break;
                case 128:
                    result = "Binary";
                    break;
            }
            return result;
        }
    }
}
